home *** CD-ROM | disk | FTP | other *** search
/ CD BIT 75 / CD BIT 75.iso / Software / mysql-4.0.22-win / data1.cab / Development / sql-bench / test-ATIS < prev    next >
Encoding:
Text File  |  2004-10-28  |  24.5 KB  |  567 lines
  1. #!/usr/bin/perl
  2. # Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
  3. #
  4. # This library is free software; you can redistribute it and/or
  5. # modify it under the terms of the GNU Library General Public
  6. # License as published by the Free Software Foundation; either
  7. # version 2 of the License, or (at your option) any later version.
  8. #
  9. # This library is distributed in the hope that it will be useful,
  10. # but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  12. # Library General Public License for more details.
  13. #
  14. # You should have received a copy of the GNU Library General Public
  15. # License along with this library; if not, write to the Free
  16. # Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
  17. # MA 02111-1307, USA
  18. #
  19. # Test of creating the ATIS database and doing many different selects on it
  20. #
  21. # changes made for Oracle compatibility
  22. # - added Oracle to the '' to ' ' translation
  23. # - skip blank lines from the datafiles
  24. # - skip a couple of the tests in Q4 that Oracle doesn't understand
  25. ################### Standard benchmark inits ##############################
  26.  
  27. use Cwd;
  28. use DBI;
  29. use Benchmark;
  30.  
  31. $opt_loop_count=100;        # Run selects this many times
  32.  
  33. $pwd = cwd(); $pwd = "." if ($pwd eq '');
  34. require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
  35.  
  36. if ($opt_small_test)
  37. {
  38.   $opt_loop_count/=10;
  39. }
  40.  
  41. print "ATIS table test\n\n";
  42.  
  43. ####
  44. ####  Connect and start timeing
  45. ####
  46.  
  47. $dbh = $server->connect();
  48. $start_time=new Benchmark;
  49.  
  50. ####
  51. #### Create needed tables
  52. ####
  53.  
  54. init_data();            # Get table definitions
  55.  
  56. if (!$opt_skip_create)
  57. {
  58.   print "Creating tables\n";
  59.   $loop_time= new Benchmark;
  60.   for ($ti = 0; $ti <= $#table_names; $ti++)
  61.   {
  62.     my $table_name = $table_names[$ti];
  63.     my $array_ref = $tables[$ti];
  64.  
  65.     # This may fail if we have no table so do not check answer
  66.     $sth = $dbh->do("drop table $table_name" . $server->{'drop_attr'});
  67.  
  68.     print "Creating table $table_name\n" if ($opt_verbose);
  69.     do_many($dbh,@$array_ref);
  70.   }
  71.   $end_time=new Benchmark;
  72.   print "Time for create_table (" . ($#tables+1) ."): " .
  73.     timestr(timediff($end_time, $loop_time),"all") . "\n\n";
  74.  
  75.   if ($opt_fast && defined($server->{vacuum}))
  76.   {
  77.     $server->vacuum(0,\$dbh);
  78.   }
  79.  
  80. ####
  81. #### Insert data
  82. ####
  83.  
  84.   print "Inserting data\n";
  85.  
  86.   $loop_time= new Benchmark;
  87.   $row_count=0;
  88.   $double_quotes=$server->{'double_quotes'};
  89.  
  90.   if ($opt_lock_tables)
  91.   {
  92.     @tmp=@table_names; push(@tmp,@extra_names);
  93.     print "LOCK TABLES @tmp\n" if ($opt_debug);
  94.     $sth = $dbh->do("LOCK TABLES " . join(" WRITE,", @tmp) . " WRITE") ||
  95.       die $DBI::errstr;
  96.   }
  97.  
  98.   if ($opt_fast && $server->{'limits'}->{'load_data_infile'})
  99.   {
  100.     for ($ti = 0; $ti <= $#table_names; $ti++)
  101.     {
  102.       my $table_name = $table_names[$ti];
  103.       my $file = "$pwd/Data/ATIS/${table_name}.txt";
  104.       print "$table_name - $file\n" if ($opt_debug);
  105.       $row_count += $server->insert_file($table_name,$file,$dbh);
  106.     }
  107.   }
  108.   else
  109.   {
  110.     if ($opt_fast && $server->{transactions})
  111.     {
  112.       $dbh->{AutoCommit} = 0;
  113.       print "Transactions enabled\n" if ($opt_debug);
  114.     }
  115.  
  116.     for ($ti = 0; $ti <= $#table_names; $ti++)
  117.     {
  118.       my $table_name = $table_names[$ti];
  119.       my $array_ref = $tables[$ti];
  120.       my @table = @$array_ref;
  121.       my $insert_start = "insert into $table_name values (";
  122.  
  123.       open(DATA, "$pwd/Data/ATIS/${table_name}.txt") || die "Can't open text file: $pwd/Data/ATIS/${table_name}.txt\n";
  124.       while(<DATA>)
  125.       {
  126.     chomp;
  127.     next unless ( $_ =~ /\w/ );    # skip blank lines
  128.     my $command = $insert_start . $_ . ")";
  129.         $command = $server->fix_for_insert($command);
  130.     print "$command\n" if ($opt_debug);
  131.         $command =~ s/\\'/\'\'/g if ($double_quotes);
  132.  
  133.     $sth = $dbh->do($command) or die "Got error: $DBI::errstr when executing '$command'\n";
  134.     $row_count++;
  135.       }
  136.     }
  137.     if ($opt_fast && $server->{transactions})
  138.     {
  139.       $dbh->commit;
  140.       $dbh->{AutoCommit} = 1;
  141.     }
  142.     close(DATA);
  143.   }
  144.  
  145.   if ($opt_lock_tables)
  146.   {
  147.     $dbh->do("UNLOCK TABLES");
  148.   }
  149.   $end_time=new Benchmark;
  150.   print "Time to insert ($row_count): " .
  151.     timestr(timediff($end_time, $loop_time),"all") . "\n\n";
  152. }
  153.  
  154. if ($opt_fast && defined($server->{vacuum}))
  155. {
  156.   $server->vacuum(0,\$dbh,@table_names);
  157. }
  158.  
  159. if ($opt_lock_tables)
  160. {
  161.   @tmp=@table_names; push(@tmp,@extra_names);
  162.   $sth = $dbh->do("LOCK TABLES " . join(" READ,", @tmp) . " READ") ||
  163.     die $DBI::errstr;
  164. }
  165. #
  166. # Now the fun begins.  Let's do some simple queries on the result
  167. #
  168. # The query array is defined as:
  169. # query, number of rows in result, 0|1 where 1 means that the query is possible
  170. #
  171.  
  172. print "Retrieving data\n";
  173. @Q1=("select_simple_join",
  174.      "select city.city_name,state.state_name,city.city_code from city,state where city.city_code='MATL' and city.state_code=state.state_code",1,1,
  175.      "select city.city_name,state.state_name,city.city_code from state,city where city.state_code=state.state_code",11,1,
  176.      "select month_name.month_name,day_name.day_name from month_name,day_name where month_name.month_number=day_name.day_code",7,1,
  177.      "select month_name.month_name,day_name.day_name from month_name,day_name where month_name.month_number=day_name.day_code and day_name.day_code >= 4",4,1,
  178.      "select flight.flight_code,aircraft.aircraft_type from flight,aircraft where flight.aircraft_code=aircraft.aircraft_code",579,1,
  179.      );
  180.  
  181. @Q2=("select_join",
  182.      "select airline.airline_name,aircraft.aircraft_type from aircraft,airline,flight where flight.aircraft_code=aircraft.aircraft_code and flight.airline_code=airline.airline_code",579,1);
  183.  
  184. @Q21=("select_key_prefix_join",
  185.      "select fare.fare_code from restrict_carrier,airline,fare where restrict_carrier.airline_code=airline.airline_code and fare.restrict_code=restrict_carrier.restrict_code",5692,1,
  186.     );
  187.  
  188. @Q3=("select_distinct",
  189.      "select distinct category from aircraft",6,1,
  190.      "select distinct from_airport from flight",9,1,
  191.      "select distinct aircraft_code from flight",22,1,
  192.      "select distinct * from fare",534,1,
  193.      "select distinct flight_code from flight_fare",579,1,
  194.      "select distinct flight.flight_code,aircraft.aircraft_type from flight,aircraft where flight.aircraft_code=aircraft.aircraft_code",579,1,
  195.      "select distinct airline.airline_name,aircraft.aircraft_type from aircraft,airline,flight where flight.aircraft_code=aircraft.aircraft_code and flight.airline_code=airline.airline_code",44,$limits->{'join_optimizer'},
  196.      "select distinct airline.airline_name,aircraft.aircraft_type from flight,aircraft,airline where flight.aircraft_code=aircraft.aircraft_code and flight.airline_code=airline.airline_code",44,1,
  197.      );
  198.  
  199. @Q4=("select_group",
  200.      "select day_name.day_name,day_name.day_code,count(*) from flight_day,day_name where day_name.day_code=flight_day.day_code group by day_name.day_name,day_name.day_code order by day_name.day_code",7,$limits->{'group_functions'},
  201.      "select day_name.day_name,count(*) from flight_day,day_name where day_name.day_code=flight_day.day_code group by day_name.day_name",7,$limits->{'group_functions'},
  202.      "select month_name,day_name from month_name,day_name where month_number=day_code and day_code>3 group by month_name,day_name",4,$limits->{'group_functions'},
  203.      "select day_name.day_name,flight_day.day_code,count(*) from flight_day,day_name where day_name.day_code=flight_day.day_code group by flight_day.day_code,day_name.day_name order by flight_day.day_code",7,$limits->{'group_functions'},
  204.      "select sum(engines) from aircraft",1,$limits->{'group_functions'},
  205.      "select avg(engines) from aircraft",1,$limits->{'group_functions'},
  206.      "select avg(engines) from aircraft where engines>0",1,$limits->{'group_functions'},
  207.      "select count(*),min(pay_load),max(pay_load) from aircraft where pay_load>0",1,$limits->{'group_functions'},
  208.      "select min(flight_code),min(flight_code) from flight",1,$limits->{'group_functions'},
  209.      "select min(from_airport),min(to_airport) from flight",1,$limits->{'group_functions'} && $limits->{'group_func_sql_min_str'},
  210.      "select count(*) from aircraft where pay_load>10000",1,$limits->{'group_functions'},
  211.      "select count(*) from aircraft where pay_load<>0",1,$limits->{'group_functions'},
  212.      "select count(*) from flight where flight_code >= 112793",1,$limits->{'group_functions'},
  213.      "select count(if(pay_load,1,NULL)) from aircraft",1,$limits->{'if'} && $limits->{'group_functions'},
  214.      "select std(engines) from aircraft",1,$limits->{'group_func_extra_std'},
  215.      "SELECT from_airport,to_airport,avg(time_elapsed) FROM flight WHERE from_airport='ATL' AND to_airport='BOS' group by from_airport,to_airport",1,$limits->{'group_functions'},
  216.      "select city_code, avg(ground_fare) from ground_service where ground_fare<>0 group by city_code",11,$limits->{'group_functions'},
  217.      "select count(*), ground_service.city_code from ground_service group by ground_service.city_code",12,$limits->{'group_functions'},
  218.      "select category,count(*) as totalnr from aircraft where engines=2 group by category having totalnr>4",3,$limits->{'group_functions'} && $limits->{'having_with_alias'},
  219.      "select category,count(*) from aircraft where engines=2 group by category having count(*)>4",3,$limits->{'group_functions'} && $limits->{'having_with_group'},
  220.      "select flight_number,range_miles,fare_class FROM aircraft,flight,flight_class WHERE flight.flight_code=flight_class.flight_code AND flight.aircraft_code=aircraft.aircraft_code AND range_miles<>0 AND (stops=1 OR stops=2) GROUP BY flight_number,range_miles,fare_class",150,$limits->{'group_functions'},
  221.      "select distinct from_airport.time_zone_code,to_airport.time_zone_code,(FLOOR(arrival_time/100)*60+MOD(arrival_time,100)-FLOOR(departure_time/100)*60-MOD(departure_time,100)-time_elapsed)/60 AS time_zone_diff FROM flight,airport AS from_airport,airport AS to_airport WHERE flight.from_airport=from_airport.airport_code AND flight.to_airport=to_airport.airport_code GROUP BY from_airport.time_zone_code,to_airport.time_zone_code,arrival_time,departure_time,time_elapsed",21,$limits->{'func_odbc_mod'} && $limits->{'func_odbc_floor'} && $limits->{'group_functions'},
  222.      "select DISTINCT from_airport.time_zone_code,to_airport.time_zone_code,MOD((FLOOR(arrival_time/100)*60+MOD(arrival_time,100)-FLOOR(departure_time/100)*60-MOD(departure_time,100)-time_elapsed)/60+36,24)-12 AS time_zone_diff FROM flight,airport AS from_airport,airport AS to_airport WHERE flight.from_airport=from_airport.airport_code AND flight.to_airport=to_airport.airport_code and MOD((FLOOR(arrival_time/100)*60+MOD(arrival_time,100)-FLOOR(departure_time/100)*60-MOD(departure_time,100)-time_elapsed)/60+36,24)-12 < 10",14,$limits->{'func_odbc_mod'} && $limits->{'func_odbc_floor'} && $limits->{'group_functions'},
  223.      "select from_airport,to_airport,range_miles,time_elapsed FROM aircraft,flight WHERE aircraft.aircraft_code=flight.aircraft_code AND to_airport NOT LIKE from_airport AND range_miles<>0 AND time_elapsed<>0 GROUP BY from_airport,to_airport,range_miles,time_elapsed",409,$limits->{'group_functions'} && $limits->{'like_with_column'},
  224.      "SELECT airport.country_name,state.state_name,city.city_name,airport_service.direction FROM airport_service,state,airport,city WHERE airport_service.city_code=city.city_code AND airport_service.airport_code=airport.airport_code AND state.state_code=airport.state_code AND state.state_code=city.state_code AND airport.state_code=city.state_code AND airport.country_name=city.country_name AND airport.country_name=state.country_name AND city.time_zone_code=airport.time_zone_code GROUP BY airport.country_name,state.state_name,city.city_name,airport_service.direction ORDER BY state_name",11,$limits->{'group_functions'},
  225.      "SELECT airport.country_name,state.state_name,city.city_name,airport_service.direction FROM airport_service,state,airport,city WHERE airport_service.city_code=city.city_code AND airport_service.airport_code=airport.airport_code AND state.state_code=airport.state_code AND state.state_code=city.state_code AND airport.state_code=city.state_code AND airport.country_name=city.country_name AND airport.country_name=state.country_name AND city.time_zone_code=airport.time_zone_code GROUP BY airport.country_name,state.state_name,city.city_name,airport_service.direction ORDER BY state_name DESC",11,$limits->{'group_functions'},
  226.      "SELECT airport.country_name,state.state_name,city.city_name,airport_service.direction FROM airport_service,state,airport,city WHERE airport_service.city_code=city.city_code AND airport_service.airport_code=airport.airport_code AND state.state_code=airport.state_code AND state.state_code=city.state_code AND airport.state_code=city.state_code AND airport.country_name=city.country_name AND airport.country_name=state.country_name AND city.time_zone_code=airport.time_zone_code GROUP BY airport.country_name,state.state_name,city.city_name,airport_service.direction ORDER BY state_name",11,$limits->{'group_functions'},
  227.      "SELECT from_airport,to_airport,fare.fare_class,night,one_way_cost,rnd_trip_cost,class_days FROM compound_class,fare WHERE compound_class.fare_class=fare.fare_class AND one_way_cost <= 825 AND one_way_cost >= 280 AND from_airport='SFO' AND to_airport='DFW' GROUP BY from_airport,to_airport,fare.fare_class,night,one_way_cost,rnd_trip_cost,class_days ORDER BY one_way_cost",10,$limits->{'group_functions'},
  228.      "select engines,category,cruising_speed,from_airport,to_airport FROM aircraft,flight WHERE category='JET' AND engines >= 1 AND aircraft.aircraft_code=flight.aircraft_code AND to_airport NOT LIKE from_airport AND stops>0 GROUP BY engines,category,cruising_speed,from_airport,to_airport ORDER BY engines DESC",29,$limits->{'group_functions'} && $limits->{'like_with_column'},
  229.      );
  230.  
  231. @Q=(\@Q1,\@Q2,\@Q21,\@Q3,\@Q4);
  232.  
  233.  
  234. foreach $Q (@Q)
  235. {
  236.   $count=$estimated=0;
  237.   $loop_time= new Benchmark;
  238.   for ($i=1 ; $i <= $opt_loop_count; $i++)
  239.   {
  240.     for ($j=1 ; $j < $#$Q ; $j+=3)
  241.     {
  242.       if ($Q->[$j+2])
  243.       {                # We can do it with current limits
  244.     $count++;
  245.     if ($i == 100)        # Do something different
  246.     {
  247.       if (($row_count=fetch_all_rows($dbh,$server->query($Q->[$j]))) !=
  248.           $Q->[$j+1])
  249.       {
  250.         if ($row_count == undef())
  251.         {
  252.           die "Got error: $DBI::errstr when executing " . $Q->[$j] ."\n"."got $row_count instead of $Q->[$j+1] *** \n";
  253.         }
  254.         print "Warning: Query '" . $Q->[$j] . "' returned $row_count rows when it should have returned " . $Q->[$j+1] . " rows\n";
  255.       }
  256.     }
  257.     else
  258.     {
  259.       defined(fetch_all_rows($dbh,$server->query($Q->[$j])))
  260.         or die "ERROR: $DBI::errstr executing '$Q->[$j]'\n";
  261.     }
  262.       }
  263.     }
  264.     $end_time=new Benchmark;
  265.     last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i,
  266.                        $opt_loop_count));
  267.     print "Loop $i\n" if ($opt_verbose);
  268.   }
  269.   if ($count)
  270.   {
  271.     if ($estimated)
  272.     { print "Estimated time"; }
  273.     else
  274.     { print "Time"; }
  275.     print  " for " . $Q->[0] . " ($count): " .
  276.       timestr(timediff($end_time, $loop_time),"all") . "\n";
  277.   }
  278. }
  279.  
  280. print "\n";
  281.  
  282. ####
  283. #### Delete the tables
  284. ####
  285.  
  286. if (!$opt_skip_delete)                # Only used when testing
  287. {
  288.   print "Removing tables\n";
  289.   $loop_time= new Benchmark;
  290.   if ($opt_lock_tables)
  291.   {
  292.     $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
  293.   }
  294.   for ($ti = 0; $ti <= $#table_names; $ti++)
  295.   {
  296.     my $table_name = $table_names[$ti];
  297.     $sth = $dbh->do("drop table $table_name" . $server->{'drop_attr'});
  298.   }
  299.  
  300.   $end_time=new Benchmark;
  301.   print "Time to drop_table (" .($#table_names+1) . "): " .
  302.     timestr(timediff($end_time, $loop_time),"all") . "\n";
  303. }
  304.  
  305. if ($opt_fast && defined($server->{vacuum}))
  306. {
  307.   $server->vacuum(0,\$dbh);
  308. }
  309.  
  310. ####
  311. #### End of benchmark
  312. ####
  313.  
  314. $dbh->disconnect;                # close connection
  315.  
  316. end_benchmark($start_time);
  317.  
  318.  
  319. sub init_data
  320. {
  321.   @aircraft=
  322.     $server->create("aircraft",
  323.             ["aircraft_code char(3) NOT NULL",
  324.              "aircraft_type char(64) NOT NULL",
  325.              "engines tinyint(1) NOT NULL",
  326.              "category char(10) NOT NULL",
  327.              "wide_body char(3) NOT NULL",
  328.              "wing_span float(6,2) NOT NULL",
  329.              "length1 float(6,2) NOT NULL",
  330.              "weight integer(7) NOT NULL",
  331.              "capacity smallint(3) NOT NULL",
  332.              "pay_load integer(7) NOT NULL",
  333.              "cruising_speed mediumint(5) NOT NULL",
  334.              "range_miles mediumint(5) NOT NULL",
  335.              "pressurized char(3) NOT NULL"],
  336.             ["PRIMARY KEY (aircraft_code)"]);
  337.   @airline=
  338.     $server->create("airline",
  339.             ["airline_code char(2) NOT NULL",
  340.              "airline_name char(64) NOT NULL",
  341.              "notes char(38) NOT NULL"],
  342.             ["PRIMARY KEY (airline_code)"]);
  343.   @airport=
  344.     $server->create("airport",
  345.             ["airport_code char(3) NOT NULL",
  346.              "airport_name char(40) NOT NULL",
  347.              "location char(36) NOT NULL",
  348.              "state_code char(2) NOT NULL",
  349.              "country_name char(25) NOT NULL",
  350.              "time_zone_code char(3) NOT NULL"],
  351.             ["PRIMARY KEY (airport_code)"]);
  352.   @airport_service=
  353.     $server->create("airport_service",
  354.             ["city_code char(4) NOT NULL",
  355.              "airport_code char(3) NOT NULL",
  356.              "miles_distant float(4,1) NOT NULL",
  357.              "direction char(3) NOT NULL",
  358.              "minutes_distant smallint(3) NOT NULL"],
  359.             ["PRIMARY KEY (city_code, airport_code)"]);
  360.   @city=
  361.     $server->create("city",
  362.             ["city_code char(4) NOT NULL",
  363.              "city_name char(25) NOT NULL",
  364.              "state_code char(2) NOT NULL",
  365.              "country_name char(25) NOT NULL",
  366.              "time_zone_code char(3) NOT NULL"],
  367.             ["PRIMARY KEY (city_code)"]);
  368.   @class_of_service=
  369.     $server->create("class_of_service",
  370.             ["class_code char(2) NOT NULL",
  371.              "rank tinyint(2) NOT NULL",
  372.              "class_description char(80) NOT NULL"],
  373.             ["PRIMARY KEY (class_code)"]);
  374.   @code_description=
  375.     $server->create("code_description",
  376.             ["code char(5) NOT NULL",
  377.              "description char(110) NOT NULL"],
  378.             ["PRIMARY KEY (code)"]);
  379.   @compound_class=
  380.     $server->create("compound_class",
  381.             ["fare_class char(3) NOT NULL",
  382.              "base_class char(2) NOT NULL",
  383.              "class_type char(10) NOT NULL",
  384.              "premium char(3) NOT NULL",
  385.              "economy char(3) NOT NULL",
  386.              "discounted char(3) NOT NULL",
  387.              "night char(3) NOT NULL",
  388.              "season_fare char(4) NOT NULL",
  389.              "class_days char(7) NOT NULL"],
  390.             ["PRIMARY KEY (fare_class)"]);
  391.   @connect_leg=
  392.     $server->create("connect_leg",
  393.             ["connect_code integer(8) NOT NULL",
  394.              "leg_number tinyint(1) NOT NULL",
  395.              "flight_code integer(8) NOT NULL"],
  396.             ["PRIMARY KEY (connect_code, leg_number, flight_code)"]);
  397.   @connection=
  398.     $server->create("fconnection",
  399.             ["connect_code integer(8) NOT NULL",
  400.              "from_airport char(3) NOT NULL",
  401.              "to_airport char(3) NOT NULL",
  402.              "departure_time smallint(4) NOT NULL",
  403.              "arrival_time smallint(4) NOT NULL",
  404.              "flight_days char(7) NOT NULL",
  405.              "stops tinyint(1) NOT NULL",
  406.              "connections tinyint(1) NOT NULL",
  407.              "time_elapsed smallint(4) NOT NULL"],
  408.             ["PRIMARY KEY (connect_code)",
  409.              "INDEX from_airport1 (from_airport)",
  410.              "INDEX to_airport1 (to_airport)"]);
  411.   @day_name=
  412.     $server->create("day_name",
  413.             ["day_code tinyint(1) NOT NULL",
  414.              "day_name char(9) NOT NULL"],
  415.             ["PRIMARY KEY (day_code)"]);
  416.   @dual_carrier=
  417.     $server->create("dual_carrier",
  418.             ["main_airline char(2) NOT NULL",
  419.              "dual_airline char(2) NOT NULL",
  420.              "low_flight smallint(4) NOT NULL",
  421.              "high_flight smallint(4) NOT NULL",
  422.              "fconnection_name char(64) NOT NULL"],
  423.             ["PRIMARY KEY (main_airline, dual_airline, low_flight)",
  424.              "INDEX main_airline1 (main_airline)"]);
  425.  
  426.   @fare=
  427.     $server->create("fare",
  428.             ["fare_code char(8) NOT NULL",
  429.              "from_airport char(3) NOT NULL",
  430.              "to_airport char(3) NOT NULL",
  431.              "fare_class char(3) NOT NULL",
  432.              "fare_airline char(2) NOT NULL",
  433.              "restrict_code char(5) NOT NULL",
  434.              "one_way_cost float(7,2) NOT NULL",
  435.              "rnd_trip_cost float(8,2) NOT NULL"],
  436.             ["PRIMARY KEY (fare_code)",
  437.              "INDEX from_airport2 (from_airport)",
  438.              "INDEX to_airport2 (to_airport)"]);
  439.   @flight=
  440.     $server->create("flight",
  441.             ["flight_code integer(8) NOT NULL",
  442.              "flight_days char(7) NOT NULL",
  443.              "from_airport char(3) NOT NULL",
  444.              "to_airport char(3) NOT NULL",
  445.              "departure_time smallint(4) NOT NULL",
  446.              "arrival_time smallint(4) NOT NULL",
  447.              "airline_code char(2) NOT NULL",
  448.              "flight_number smallint(4) NOT NULL",
  449.              "class_string char(8) NOT NULL",
  450.              "aircraft_code char(3) NOT NULL",
  451.              "meal_code char(7) NOT NULL",
  452.              "stops tinyint(1) NOT NULL",
  453.              "dual_carrier char(1) NOT NULL",
  454.              "time_elapsed smallint(4) NOT NULL"],
  455.             ["PRIMARY KEY (flight_code)",
  456.              "INDEX from_airport3 (from_airport)",
  457.              "INDEX to_airport3 (to_airport)"]);
  458.   @flight_class=
  459.     $server->create("flight_class",
  460.             ["flight_code integer(8) NOT NULL",
  461.              "fare_class char(3) NOT NULL"],
  462.             ["PRIMARY KEY (flight_code, fare_class)"]);
  463.   @flight_day=
  464.     $server->create("flight_day",
  465.             ["day_mask char(7) NOT NULL",
  466.              "day_code tinyint(1) NOT NULL",
  467.              "day_name char(9) NOT NULL"],
  468.             ["PRIMARY KEY (day_mask, day_code)"]);
  469.   @flight_fare=
  470.     $server->create("flight_fare",
  471.             ["flight_code integer(8) NOT NULL",
  472.              "fare_code char(8) NOT NULL"],
  473.             ["PRIMARY KEY (flight_code, fare_code)"]);
  474.   @food_service=
  475.     $server->create("food_service",
  476.             ["meal_code char(4) NOT NULL",
  477.              "meal_number tinyint(1) NOT NULL",
  478.              "meal_class char(10) NOT NULL",
  479.              "meal_description char(10) NOT NULL"],
  480.             ["PRIMARY KEY (meal_code, meal_number, meal_class)"]);
  481.   @ground_service=
  482.     $server->create("ground_service",
  483.             ["city_code char(4) NOT NULL",
  484.              "airport_code char(3) NOT NULL",
  485.              "transport_code char(1) NOT NULL",
  486.              "ground_fare float(6,2) NOT NULL"],
  487.             ["PRIMARY KEY (city_code, airport_code, transport_code)"]);
  488.   @time_interval=
  489.     $server->create("time_interval",
  490.             ["period char(20) NOT NULL",
  491.              "begin_time smallint(4) NOT NULL",
  492.              "end_time smallint(4) NOT NULL"],
  493.             ["PRIMARY KEY (period, begin_time)"]);
  494.   @month_name=
  495.     $server->create("month_name",
  496.             ["month_number tinyint(2) NOT NULL",
  497.              "month_name char(9) NOT NULL"],
  498.             ["PRIMARY KEY (month_number)"]);
  499.   @restrict_carrier=
  500.     $server->create("restrict_carrier",
  501.             ["restrict_code char(5) NOT NULL",
  502.              "airline_code char(2) NOT NULL"],
  503.             ["PRIMARY KEY (restrict_code, airline_code)"]);
  504.   @restrict_class=
  505.     $server->create("restrict_class",
  506.             ["restrict_code char(5) NOT NULL",
  507.              "ex_fare_class char(12) NOT NULL"],
  508.             ["PRIMARY KEY (restrict_code, ex_fare_class)"]);
  509.   @restriction=
  510.     $server->create("restriction",
  511.             ["restrict_code char(5) NOT NULL",
  512.              "application char(80) NOT NULL",
  513.              "no_discounts char(80) NOT NULL",
  514.              "reserve_ticket smallint(3) NOT NULL",
  515.              "stopovers char(1) NOT NULL",
  516.              "return_min smallint(3) NOT NULL",
  517.              "return_max smallint(3) NOT NULL"],
  518.             ["PRIMARY KEY (restrict_code)"]);
  519.   @state=
  520.     $server->create("state",
  521.             ["state_code char(2) NOT NULL",
  522.              "state_name char(25) NOT NULL",
  523.              "country_name char(25) NOT NULL"],
  524.             ["PRIMARY KEY (state_code)"]);
  525.   @stop=
  526.     $server->create("stop1",
  527.             ["flight_code integer(8) NOT NULL",
  528.              "stop_number tinyint(1) NOT NULL",
  529.              "stop_flight integer(8) NOT NULL"],
  530.             ["PRIMARY KEY (flight_code, stop_number)"]);
  531.   @time_zone=
  532.     $server->create("time_zone",
  533.             ["time_zone_code char(3) NOT NULL",
  534.              "time_zone_name char(32) NOT NULL"],
  535.             ["PRIMARY KEY (time_zone_code, time_zone_name)"]);
  536.   @transport=
  537.     $server->create("transport",
  538.             ["transport_code char(1) NOT NULL",
  539.              "transport_desc char(32) NOT NULL"],
  540.             ["PRIMARY KEY (transport_code)"]);
  541.  
  542. # Avoid not used warnings
  543.  
  544.   @tables =
  545.     (\@aircraft, \@airline, \@airport, \@airport_service,
  546.      \@city, \@class_of_service, \@code_description,
  547.      \@compound_class, \@connect_leg, \@connection, \@day_name,
  548.      \@dual_carrier, \@fare, \@flight, \@flight_class, \@flight_day,
  549.      \@flight_fare, \@food_service, \@ground_service, \@time_interval,
  550.      \@month_name,
  551.      \@restrict_carrier, \@restrict_class, \@restriction, \@state, \@stop,
  552.      \@time_zone, \@transport);
  553.  
  554.   @table_names =
  555.     ("aircraft", "airline", "airport", "airport_service",
  556.      "city", "class_of_service", "code_description",
  557.      "compound_class", "connect_leg", "fconnection", "day_name",
  558.      "dual_carrier", "fare", "flight", "flight_class", "flight_day",
  559.      "flight_fare", "food_service", "ground_service", "time_interval",
  560.      "month_name",
  561.      "restrict_carrier", "restrict_class", "restriction", "state", "stop1",
  562.      "time_zone", "transport");
  563.  
  564. # Alias used in joins
  565.   @extra_names=("airport as from_airport","airport as to_airport");
  566. }
  567.